/**
 * 
 */
package com.gkwave.module.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.gkwave.module.bean.MessageBean;
import com.gkwave.module.bean.TransportRegisterBean;

/**
 * @author Administrator
 * 
 */
public class TransportRegisterDAO extends BaseDAO {

	private Statement stmt = null;
	private ResultSet rset = null;
	private Connection con = null;

	/**
	 * 
	 */
	public TransportRegisterDAO() {
		// TODO Auto-generated constructor stub
	}

	public MessageBean doRegister(Integer idTransportService, 
								  Integer idRoute, 
								  Integer idBoardingPoint, 
								  Integer idUser, 
								  String nmMobileNumber){
		MessageBean bean = null;
		try {
			Integer nrTotalSeatCapacity = getTotalSeatCapacity(idTransportService);
			Integer nrBookedSeat = getBookedSeat(idTransportService,idRoute);
			
			if(isSeatAvailable(nrTotalSeatCapacity, nrBookedSeat)){
				System.out.println("Seats Available: " );
				if(activeRegistration(idTransportService, idRoute, idBoardingPoint, idUser, nmMobileNumber)){
					deactiveRegistration(idUser, getMaxidTransportRegistration());
//					if (isSuccess){
						bean = new MessageBean("Success","Registration has been done.");
//					}else{
//						deleteRegistrationById(getMaxidTransportRegistration());
//						System.out.println("Error... " );
//						bean = new MessageBean("Error","Error...");
//					}
				}else{
					System.out.println("Error... " );
					bean = new MessageBean("Error","Error...");
				}
				
			}else {
				System.out.println("Seat is not Available: " );
				bean = new MessageBean("Error","Seat is not available. Please select other transport service location");
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bean;
	}
	
/*	private boolean deleteRegistrationById(Integer maxidTransportRegistration) {
		// TODO Auto-generated method stub
		String query = "DELETE FROM transport_registration WHERE id_transport_registration = " + maxidTransportRegistration;
		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();			
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnValue;
	}*/

	/**
	 * Checking seat availability with  reference of ID_LOGIN,ID_SERVICE,DT_TRAVEL,ID_LOCATION,IN_UP
	 * Hidden condition - isAlreadyBooked with  reference of ID_LOGIN,ID_SERVICE,DT_TRAVEL,ID_LOCATION,IN_UP
	 */
	private Integer getTotalSeatCapacity(Integer idTransService) throws Exception{
		Integer nrTotalSeatCapacity = 0;
		BaseDAO dao = new BaseDAO();
		String query = "SELECT t1.nr_available_seat FROM m_transport_service t1 WHERE ID_transport_SERVICE = " + idTransService;
		try {
			con = dao.openConnection();
			stmt = con.createStatement();			
			rset = stmt.executeQuery(query);
			if (rset.next()) {
				nrTotalSeatCapacity = rset.getInt("t1.nr_available_seat");
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			}
		}
		return nrTotalSeatCapacity;
	}

	private Integer getBookedSeat(Integer idTransportService, Integer idRoute){
		String query = "SELECT COUNT(*) AS BOOKED_SEAT FROM transport_registration t WHERE id_transport_service= "
				+ idTransportService
				+ " AND id_route= "
				+ idRoute
				+ " AND in_active='Y'";
		System.out.println(query);
		Integer nrBookedSeat = 0;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);
			if (rset.next()) {
				nrBookedSeat = rset.getInt("BOOKED_SEAT");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return nrBookedSeat;
	}

	private boolean isSeatAvailable(Integer nrTotalSeatCapacity, Integer nrBookedSeat){
		System.out.println("Total seat: " +nrTotalSeatCapacity+ " , Booked seat: " +nrBookedSeat);
		
		boolean isAvailable = false;
		if(nrTotalSeatCapacity > nrBookedSeat){
			isAvailable = true;
		}
		return isAvailable;
	}
	
	public boolean activeRegistration(Integer idTransportService,
									  Integer idRoute,
									  Integer idBoardingPoint,
									  Integer idUser,
									  String nmMobileNumber) {
		String query = "INSERT INTO transport_registration (id_transport_service,id_route,id_boarding_point,id_user,in_active,dt_active,nm_mobile_number) VALUES ("
				+ idTransportService + "," + idRoute + ","+ idBoardingPoint + "," + idUser + ",'Y',CURDATE(),'"+nmMobileNumber+"')";
		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue =  false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnValue;
	}

	public boolean deactiveRegistration(Integer idUser, Integer idTransportRegistration) {
		String query = "UPDATE transport_registration SET in_active = 'N',dt_deactive=CURDATE() WHERE id_user="
				+ idUser
				+ " and in_active='Y' and id_transport_registration <> "
				+ idTransportRegistration;
		System.out.println(query);
		boolean returnValue = false;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			int row = stmt.executeUpdate(query);
			if (row != 0) {
				returnValue = true;
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			returnValue = false;
		} finally {
			try {
				dao.closeConnection(stmt, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return returnValue;
	}

	public Object getActiveRegistration(Integer idUser) throws Exception {
		String query = "SELECT t1.id_transport_registration,t2.nm_transport_service,t3.nm_route,t4.nm_fullname, t5.nm_boarding_location "
				+ "FROM transport_registration t1, m_transport_service t2 , m_route t3 , m_user t4, boarding_location t5 "
				+ "where t1.id_transport_service=t2.id_transport_service and t1.id_transport_service=t2.id_transport_service"
				+ " and t1.id_route=t3.id_route and t1.id_route=t3.id_route and t4.id_user="
				+ idUser
				+ ""
				+ " and t1.id_user=t4.id_user and t1.in_active='Y' and t5.id_boarding_location=t1.id_boarding_point";
		System.out.println("Query: " + query);
		TransportRegisterBean bean = null;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);

			if (!rset.wasNull()) {
				System.out.println("result-set is not null");
			}
			while (rset.next()) {
				Integer id_transport_registration = rset.getInt("t1.id_transport_registration");
				String nm_transport_service = rset.getString("t2.nm_transport_service");
				String nm_route = rset.getString("t3.nm_route");
				String nm_fullname = rset.getString("t4.nm_fullname");
				String nm_boarding_location = rset.getString("t5.nm_boarding_location");
				bean = new TransportRegisterBean(id_transport_registration, nm_transport_service, nm_route, nm_boarding_location, nm_fullname);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			}
		}
		return bean;
	}
	
	private Integer getMaxidTransportRegistration() {
		Integer maxId = 0;
		String query = "SELECT max(id_transport_registration) as MaxId FROM transport_registration";
		System.out.println(query);
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();			
			rset = stmt.executeQuery(query);
			if (rset.next()) {
				maxId = rset.getInt("MaxId");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			try {
				dao.closeConnection(stmt, con , rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return maxId;
	}
	
	public TransportRegisterBean getRegistrationInfo(Integer idUser) throws Exception {
		String query = "SELECT " +
							"t1.id_transport_registration," +
							"t2.nm_transport_service," +
							"t3.nm_route," +
							"t4.nm_fullname," +
							"t1.nm_mobile_number," +
							"t6.nm_boarding_location," +
							"t5.ts_up_trip," +
							"t2.id_email " +
					   "FROM " +
					   		"transport_registration t1," +
					   		"m_transport_service t2," +
					   		"m_route t3," +
					   		"m_user t4," +
					   		"transport_route t5," +
					   		"boarding_location t6 " +
					   "WHERE " +
					   		"t2.id_transport_service=t1.id_transport_service and " +
					   		"t1.id_route=t3.id_route and " +
					   		"t1.id_user=t4.id_user and " +
					   		"t5.id_location=t1.id_boarding_point and " +
					   		"t6.id_boarding_location=t1.id_boarding_point and " +
					   		"t1.in_active='Y' and " +
					   		"t1.id_user  =" + idUser;
		
		System.out.println("Query: " + query);
		TransportRegisterBean bean = null;
		BaseDAO dao = new BaseDAO();
		try {
			con = dao.openConnection();
			stmt = con.createStatement();
			rset = stmt.executeQuery(query);

			if (!rset.wasNull()) {
				System.out.println("result-set is not null");
			}
			while (rset.next()) {
				Integer id_transport_registration = rset.getInt("t1.id_transport_registration");
				String nm_transport_service = rset.getString("t2.nm_transport_service");
				String nm_route = rset.getString("t3.nm_route");
				String nm_fullname = rset.getString("t4.nm_fullname");
				String nm_boarding_location = rset.getString("t6.nm_boarding_location");
				String nm_mobile_number = rset.getString("t1.nm_mobile_number");
				String tsTripTime = rset.getString("t5.ts_up_trip");
				String idEmail = rset.getString("t2.id_email");
				bean = new TransportRegisterBean(id_transport_registration, nm_transport_service, nm_route, nm_boarding_location, nm_fullname,nm_mobile_number,tsTripTime,idEmail);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new Exception(e);
		} finally {
			try {
				dao.closeConnection(stmt, con, rset);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				throw new Exception(e);
			}
		}
		return bean;
	}
}
